package com.liu.dao.user;

import com.liu.dao.BaseDao;
import com.liu.pojo.Role;
import com.liu.pojo.User;
import com.mysql.cj.util.StringUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserDaoImpl implements UserDao{

    @Override
    public User getLoginUser(Connection connection, String userCode, String userPassword) throws SQLException {

        PreparedStatement pstm = null;
        ResultSet rs = null;
        User user = null;



        if (connection!=null){
            String sql = "select * from smbms_user where userCode=? and userPassword=?";
            Object[] params = {userCode, userPassword};


                rs = BaseDao.execute(connection, pstm, rs, sql, params);

                if (rs.next()){
                    user = new User();
                    user.setId(rs.getInt("id"));
                    user.setUserCode(rs.getString("userCode"));
                    user.setUserPassword(rs.getString("userPassword"));
                    user.setUserName(rs.getString("userName"));
                    user.setGender(rs.getInt("gender"));
                    user.setBirthday(rs.getDate("birthday"));
                    user.setPhone(rs.getString("phone"));
                    user.setAddress(rs.getString("address"));
                    user.setUserRole(rs.getInt("userRole"));
                    user.setCreatedBy(rs.getInt("createdBy"));
                    user.setCreationDate(rs.getDate("creationDate"));
                    user.setModifyBy(rs.getInt("modifyBy"));
                    user.setModifyDate(rs.getDate("modifyDate"));
                }
                BaseDao.closeResource(null,pstm,rs);

        }

        return user;
    }

    //修改密码
    @Override
    public int updatePwd(Connection connection, int id, String password) throws SQLException {

        PreparedStatement pstm = null;
        int execute = 0;
        if (connection != null){
            String sql = "update smbms_user set userPassword = ? where id = ?";
            Object params[] = {password,id};
            execute = BaseDao.execute(connection, pstm, sql, params);
            BaseDao.closeResource(null,pstm,null);
        }


        return execute;
    }

    //查询用户

    @Override
    public int getUserCount(Connection connection, String userName, int userRole) throws SQLException {
        PreparedStatement pstm = null;
        ResultSet rs = null;
        int count = 0;
        if (connection!=null){
            StringBuffer sql = new StringBuffer();
            sql.append("select count(1) as count from smbms_user u,smbms_role r where u.userRole = r.id");
            ArrayList<Object> list = new ArrayList<Object>();

            if(!StringUtils.isNullOrEmpty(userName)){
                sql.append(" and u.userName like ?");
                list.add("%"+userName+"%");
            }

            if(userRole > 0){
                sql.append(" and u.userRole = ?");
                list.add(userRole);//index: 1
            }

            //将list转换为数组
            Object[] objects = list.toArray();

            System.out.println("UserDaoImpl -> getUserCount:" + sql.toString());

            rs=BaseDao.execute(connection, pstm, rs, sql.toString(), objects);

            if(rs.next()){
                count = rs.getInt("count");//从结果集中获取数量
            }

            BaseDao.closeResource(null,pstm,rs);
        }
        return count;
    }

    @Override
    public List<User> getUserList(Connection connection, String userName, int userRole, int currentPageNo, int pageSize) throws SQLException {
        List<User> users = new ArrayList();
        if (connection == null) {
            return users;
        } else {
            PreparedStatement preparedStatement = null;
            ResultSet rs = null;
            StringBuilder sql = new StringBuilder();
            sql.append("select u.*, r.roleName as userRoleName from smbms_user u, smbms_role r where u.userRole = r.id");
            List<Object> list = new ArrayList();
            if (!StringUtils.isNullOrEmpty(userName)) {
                sql.append(" and u.userName like ?");
                list.add("%" + userName + "%");
            }

            if (userRole > 0) {
                sql.append(" and u.userRole = ?");
                list.add(userRole);
            }

            sql.append(" order by creationDate DESC limit ?, ?");
            currentPageNo = (currentPageNo - 1) * pageSize;
            list.add(currentPageNo);
            list.add(pageSize);
            Object[] params = list.toArray();
            rs = BaseDao.execute(connection, (PreparedStatement)preparedStatement, rs, sql.toString(), params);

            while(rs.next()) {
                User user = new User();
                user.setId(rs.getInt("id"));
                user.setUserCode(rs.getString("userCode"));
                user.setUserName(rs.getString("userName"));
                user.setGender(rs.getInt("gender"));
                user.setBirthday(rs.getDate("birthday"));
                user.setPhone(rs.getString("phone"));
                user.setUserRole(rs.getInt("userRole"));
                user.setUserRoleName(rs.getString("userRoleName"));
                users.add(user);
            }

            BaseDao.closeResource(null, (PreparedStatement)preparedStatement, rs);
            return users;
        }
    }

    @Override
    public List<Role> getRoleList(Connection connection) throws SQLException {
        PreparedStatement pstm = null;
        ResultSet rs = null;
        ArrayList<Role> roles = new ArrayList<Role>();
        if (connection == null){
            String sql = "select * from smbms_role";
            Object[] params = {};
            ResultSet st = BaseDao.execute(connection, pstm, rs, sql, params);

            while(st.next()){
                Role _role = new Role();
                _role.setId(st.getInt("id"));
                _role.setRoleCode(st.getString("roleCode"));
                _role.setRoleName(st.getString("roleName"));
                roles.add(_role);
            }
            BaseDao.closeResource(null, pstm, rs);
        }
        return roles;
    }
}
